package impl

import (
	"adam2/internal/model"
	"adam2/internal/properties"
	"adam2/internal/util"
	"fmt"
)

type Real9OptimizeStockTransactionRecordDaoImpl struct {
	*BaseDaoImpl
}

// 返回dao实现类
func GetReal9OptimizeStockTransactionRecordDaoImpl() *Real9OptimizeStockTransactionRecordDaoImpl {
	return &Real9OptimizeStockTransactionRecordDaoImpl{GetBaseDaoImpl()}
}

// 返回表名
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindTableName() string {
	return "REAL9_O_STOCK_TRANSACT_RECORD"
}

// 根据账户名，查询正在持仓的股票
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindByAccountNameAndSellNull(accountName string) model.Real9OptimizeStockTransactionRecordArray {
	var real9OptimizeStockTransactionRecordArray model.Real9OptimizeStockTransactionRecordArray
	r.db.Raw("select * from real9_o_stock_transact_record t "+
		"where t.account_name = ? and t.sell_date is null and t.sell_price is null "+
		"and t.sell_amount is null",
		accountName).Scan(&real9OptimizeStockTransactionRecordArray)
	return real9OptimizeStockTransactionRecordArray
}

// 根据账户名和日期，查询当天买入的股票
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindByAccountNameAndDateAndSellNull(accountName string, date string) model.Real9OptimizeStockTransactionRecordArray {
	var real9OptimizeStockTransactionRecordArray model.Real9OptimizeStockTransactionRecordArray
	r.db.Raw("select * from real9_o_stock_transact_record qstr "+
		"where qstr.account_name = ? and qstr.buy_date=to_date(?, 'yyyy-mm-dd') "+
		"and qstr.sell_date is null and qstr.sell_price is null and qstr.sell_amount is null",
		accountName, date).Scan(&real9OptimizeStockTransactionRecordArray)
	return real9OptimizeStockTransactionRecordArray
}

// 根据账户名和日期，查询当天卖出的股票
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindByAccountNameAndDateAndSellNotNull(accountName string, date string) model.Real9OptimizeStockTransactionRecordArray {
	var real9OptimizeStockTransactionRecordArray model.Real9OptimizeStockTransactionRecordArray
	r.db.Raw("select * from real9_o_stock_transact_record qstr "+
		"where qstr.account_name = ? and qstr.sell_date=to_date(?, 'yyyy-mm-dd') "+
		"and qstr.sell_date is not null and qstr.sell_price is not null and qstr.sell_amount is not null",
		accountName, date).Scan(&real9OptimizeStockTransactionRecordArray)
	return real9OptimizeStockTransactionRecordArray
}

// 查询某个优化账户之前卖出失败的股票
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindOptimizeResolveFailSellByAccountName(accountName string) model.Real9OptimizeStockTransactionRecordArray {
	var real9OptimizeStockTransactionRecordArray model.Real9OptimizeStockTransactionRecordArray
	r.db.Raw("select * from real9_o_stock_transact_record t "+
		"where t.account_name = ? and t.SELL_DATE is null "+
		"and t.SELL_PRICE is null and t.SELL_AMOUNT is null and t.fail_sell_reason in(1, 2)",
		accountName).Scan(&real9OptimizeStockTransactionRecordArray)
	return real9OptimizeStockTransactionRecordArray
}

// // 更新（卖出之前卖出失败的、退市卖出）
func (r *Real9OptimizeStockTransactionRecordDaoImpl) UpdateWhenSell(everyDate string, stockTransactionDataAll model.StockTransactionDataAll, real9OptimizeStockTransactionRecord model.Real9OptimizeStockTransactionRecord) {
	// 更新real9_o_stock_transact_record表的sell_date、sell_price、sell_amount和fail_sell_reason字段
	r.db.Exec("update real9_o_stock_transact_record t "+
		"set t.sell_date = to_date(?, 'yyyy-mm-dd'), t.sell_price  = ?, t.sell_amount = t.buy_amount, "+
		"t.stamp_duty = ROUND(t.buy_amount * "+fmt.Sprintf("%f", stockTransactionDataAll.OpenPrice)+" * "+fmt.Sprintf("%f", properties.Real9Properties_.StampDutyRate)+", 4), "+
		"t.registration_fee_when_sell = ?, "+
		"t.commission_when_sell = ? "+
		"where t.id_ = ?",
		everyDate, stockTransactionDataAll.ClosePrice,
		util.CalculateRegistrationFee(real9OptimizeStockTransactionRecord.StockCode, real9OptimizeStockTransactionRecord.BuyAmount),
		util.CalculateCommission(stockTransactionDataAll.ClosePrice, real9OptimizeStockTransactionRecord.BuyAmount),
		real9OptimizeStockTransactionRecord.ID)

	// 更新real9_o_stock_transact_record表的profit_and_loss和profit_and_loss_rate字段
	r.db.Exec("update real9_o_stock_transact_record t "+
		"set t.profit_and_loss = ROUND((t.sell_price * t.sell_amount) - (t.buy_price * t.buy_amount), 4), "+
		"t.profit_and_loss_rate = (t.sell_price - t.buy_price) / t.buy_price * 100 "+
		"where t.id_ = ?", real9OptimizeStockTransactionRecord.ID)
}

// 查询某个账号是否有退市的股票
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindDelistByLastTransactionDateAndAccountName(lastTransactionDate string, accountName string) model.Real9OptimizeStockTransactionRecordArray {
	var real9OptimizeStockTransactionRecordArray model.Real9OptimizeStockTransactionRecordArray
	r.db.Raw("select qstr.* from real9_o_stock_transact_record qstr "+
		"join stock_info si_ on si_.code_=qstr.stock_code and si_.last_transaction_date is not null and si_.last_transaction_date=to_date(?, 'yyyy-mm-dd') "+
		"where qstr.account_name=? and qstr.sell_date is null and qstr.sell_price is null and qstr.sell_amount is null",
		lastTransactionDate, accountName).Scan(&real9OptimizeStockTransactionRecordArray)
	return real9OptimizeStockTransactionRecordArray
}

// 通过id，更新fail_sell_reason字段
func (r *Real9OptimizeStockTransactionRecordDaoImpl) UpdateFailSellReasonById(id int, failSellReason int) {
	r.db.Exec("update real9_o_stock_transact_record t set t.fail_sell_reason=? where t.id_=?", failSellReason, id)
}

// 更新（卖出时）
func (r *Real9OptimizeStockTransactionRecordDaoImpl) UpdateWhenOptimizeSell(real9QuantStockTransactionRecord model.Real9QuantStockTransactionRecord) {
	r.db.Exec("update real9_o_stock_transact_record ostr "+
		"set ostr.sell_date=?, ostr.sell_price=?, ostr.sell_amount=?, "+
		"ostr.profit_and_loss=round(?, 4), ostr.profit_and_loss_rate=?, "+
		"ostr.stamp_duty = ?, ostr.registration_fee_when_sell = ?, ostr.commission_when_sell = ? "+
		"where ostr.account_name=? and ostr.stock_code=? and ostr.buy_date=? "+
		"and ostr.sell_date is null and ostr.sell_price is null and ostr.sell_amount is null",
		real9QuantStockTransactionRecord.SellDate, real9QuantStockTransactionRecord.SellPrice, real9QuantStockTransactionRecord.SellAmount,
		real9QuantStockTransactionRecord.ProfitAndLoss, real9QuantStockTransactionRecord.ProfitAndLossRate,
		real9QuantStockTransactionRecord.StampDuty, real9QuantStockTransactionRecord.RegistrationFeeWhenSell, real9QuantStockTransactionRecord.CommissionWhenSell,
		real9QuantStockTransactionRecord.AccountName, real9QuantStockTransactionRecord.StockCode, real9QuantStockTransactionRecord.BuyDate)
}

// 更新（减仓时）
func (r *Real9OptimizeStockTransactionRecordDaoImpl) UpdateWhenOptimizeSell_underweight(everyDate string, stockTransactionDataAll model.StockTransactionDataAll, real9OptimizeStockTransactionRecord model.Real9OptimizeStockTransactionRecord) {
	//r.db.Exec("update real9_o_stock_transact_record ostr "+
	//	"set ostr.sell_date=?, ostr.sell_price=?, ostr.sell_amount=?, "+
	//	"ostr.profit_and_loss=round(?, 2), ostr.profit_and_loss_rate=?, "+
	//	"ostr.stamp_duty = ?, ostr.registration_fee_when_sell = ?, ostr.commission_when_sell = ? "+
	//	"where ostr.account_name=? and ostr.stock_code=? and ostr.buy_date=? "+
	//	"and ostr.sell_date is null and ostr.sell_price is null and ostr.sell_amount is null",
	//	real9OptimizeStockTransactionRecord.SellDate, real9OptimizeStockTransactionRecord.SellPrice, real9OptimizeStockTransactionRecord.SellAmount,
	//	real9OptimizeStockTransactionRecord.ProfitAndLoss, real9OptimizeStockTransactionRecord.ProfitAndLossRate,
	//	real9OptimizeStockTransactionRecord.StampDuty, real9OptimizeStockTransactionRecord.RegistrationFeeWhenSell, real9OptimizeStockTransactionRecord.CommissionWhenSell,
	//	real9OptimizeStockTransactionRecord.AccountName, real9OptimizeStockTransactionRecord.StockCode, real9OptimizeStockTransactionRecord.BuyDate)
	r.db.Exec("update real9_o_stock_transact_record ostr "+
		"set ostr.sell_date=to_date(?, 'yyyy-mm-dd'), ostr.sell_price=?, ostr.sell_amount=?, "+
		"ostr.profit_and_loss=round(?, 4), ostr.profit_and_loss_rate=?, "+
		"ostr.stamp_duty = ROUND(ostr.buy_amount * "+fmt.Sprintf("%f", stockTransactionDataAll.ClosePrice)+" * "+fmt.Sprintf("%f", properties.QuantProperties_.StampDutyRate)+", 4), "+
		"ostr.registration_fee_when_sell = ?, "+
		"ostr.commission_when_sell = ? "+
		"where ostr.account_name=? and ostr.stock_code=? and ostr.buy_date=?",
		everyDate, stockTransactionDataAll.ClosePrice, real9OptimizeStockTransactionRecord.BuyAmount,
		(stockTransactionDataAll.ClosePrice-real9OptimizeStockTransactionRecord.BuyPrice)*float64(real9OptimizeStockTransactionRecord.BuyAmount), (stockTransactionDataAll.ClosePrice-real9OptimizeStockTransactionRecord.BuyPrice)/real9OptimizeStockTransactionRecord.BuyPrice*100,
		util.CalculateRegistrationFee(real9OptimizeStockTransactionRecord.StockCode, real9OptimizeStockTransactionRecord.BuyAmount),
		util.CalculateCommission(stockTransactionDataAll.ClosePrice, real9OptimizeStockTransactionRecord.BuyAmount),
		real9OptimizeStockTransactionRecord.AccountName, real9OptimizeStockTransactionRecord.StockCode, real9OptimizeStockTransactionRecord.BuyDate)
}

// 插入记录
func (r *Real9OptimizeStockTransactionRecordDaoImpl) Insert(accountName string, stockCode string, everyDate string, openPrice float64, amount int,
	registrationFeeWhenBuy float64, commissionWhenBuy float64) {
	r.db.Exec("insert into real9_o_stock_transact_record(ACCOUNT_NAME, STOCK_CODE, BUY_DATE, BUY_PRICE, BUY_AMOUNT, "+
		"REGISTRATION_FEE_WHEN_BUY, COMMISSION_WHEN_BUY) "+
		"values(?, ?, to_date(?, 'yyyy-mm-dd'), ?, ?, ?, ?)",
		accountName, stockCode, everyDate, openPrice, amount, registrationFeeWhenBuy, commissionWhenBuy)
}

// 根据账号、日期，查询卖出日期为空的记录，并按照总市值降序排列
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindByAccountNameAndDateAndSellDateNullOrderByTotalMarketValueDesc(accountName string, everyDate string) model.Real9OptimizeStockTransactionRecordArray {
	var real9OptimizeStockTransactionRecordArray model.Real9OptimizeStockTransactionRecordArray
	r.db.Raw("select t.* from real9_o_stock_transact_record t "+
		"join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(?, 'yyyy-mm-dd') "+
		"where t.account_name=? and t.sell_date is null and t.sell_price is null and t.sell_amount is null "+
		"order by stda.total_market_value desc", everyDate, accountName).Scan(&real9OptimizeStockTransactionRecordArray)
	return real9OptimizeStockTransactionRecordArray
}

// 根据账号、日期，查询卖出日期为空的记录，并且不在real9_o_stock_transact_record表中，并按照总市值降序排列（注意不能是最后一个交易日）
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindByAccountNameAndDateAndSellDateNullAndNotInReal9OptimizeStockTransactionRecordOrderByTotalMarketValueDesc(accountName string, everyDate string) model.Real9QuantStockTransactionRecordArray {
	var real9QuantSStockTransactionRecordArray model.Real9QuantStockTransactionRecordArray
	r.db.Raw("select t.* from real9_q_stock_transact_record t "+
		"join stock_transaction_data_all stda on stda.code_=t.stock_code and stda.date_=to_date(?, 'yyyy-mm-dd') "+
		"where t.account_name=? and t.sell_date is null and t.sell_price is null and t.sell_amount is null and t.stock_code not in("+
		"select t1.stock_code from real9_o_stock_transact_record t1 "+
		"where t1.sell_date is null and t1.sell_price is null and t1.sell_amount is null) "+
		"and t.stock_code not in("+
		"select si_.code_ from stock_info si_ where si_.last_transaction_date=to_date(?,'yyyy-mm-dd')) "+
		"order by stda.total_market_value asc", everyDate, accountName, everyDate).Scan(&real9QuantSStockTransactionRecordArray)
	return real9QuantSStockTransactionRecordArray
}

// 根据buy_date，删除sell_date为空的记录
func (r *Real9OptimizeStockTransactionRecordDaoImpl) DeleteBySellDateNullAndBuyDate(date string) {
	r.db.Exec("delete real9_o_stock_transact_record where buy_date=to_date(?,'yyyy-mm-dd') and sell_date is null", date)
}

// 根据sell_date，将sell_date、sell_price、sell_amount、profit_and_loss、profit_and_loss_rate、stamp_duty、registration_fee_when_sell、commission_when_sell字段改为null
func (r *Real9OptimizeStockTransactionRecordDaoImpl) UpdateNullBySellDate(date string) {
	r.db.Exec("update real9_o_stock_transact_record "+
		"set sell_date=null, sell_price=null, sell_amount=null, profit_and_loss=null, profit_and_loss_rate=null, stamp_duty=null, registration_fee_when_sell=null, commission_when_sell=null "+
		"where sell_date=to_date(?,'yyyy-mm-dd')", date)
}

// 根据accountName和stockCode，查询sell_date为空的那一条记录
func (r *Real9OptimizeStockTransactionRecordDaoImpl) FindByAccountNameAndStockCodeAndSellDateNull(accountName string, stockCode string) model.Real9OptimizeStockTransactionRecord {
	var real9OptimizeStockTransactionRecord model.Real9OptimizeStockTransactionRecord
	r.db.Raw("select t.* from real9_o_stock_transact_record t "+
		"where t.account_name=? and t.stock_code=? and t.sell_date is null and t.sell_price is null and t.sell_amount is null",
		accountName, stockCode).Scan(&real9OptimizeStockTransactionRecord)
	return real9OptimizeStockTransactionRecord
}

